import pandas as pd
import requests
from libs.MySqlUtil import MySqlUtil

from libs.Constants import Constants
from libs.Objects import Objects
from libs.StockBase import StockBase

HOT_REVIEW_URL = 'https://ozone.10jqka.com.cn/open/api/draw_lots/v1/rank/all_tab_data?date={date}'


# db4free sqlpub
# sqlpub
# stock_up_db
# stock_up_root
# 0EdgWTeNTTa8J3Qy
# mysql2.sqlpub.com:3307

class StockHotReview:
    def __init__(self):
        self.conn = MySqlUtil(**MySqlUtil.CONF)
        print(self.conn.get_dicts("SELECT VERSION()"))

    def if_exist(self, tradingDay):
        values = self.conn.get_values(
            'select 1 from hot_review where day={day}'.format(day=Objects.to_db_date(tradingDay)))
        return not len(values) == 0

    def delete_by_day(self, tradingDay):
        self.conn.execute('delete from hot_review where day={day}'.format(day=Objects.to_db_date(tradingDay)))

    def save_data(self, tradingDay):
        tradingDay = Objects.to_db_date(tradingDay)
        content = requests.get(HOT_REVIEW_URL.format(date=tradingDay), headers=Constants.headers).content

        json_obj = Objects.toJson(content)
        sql = "insert into hot_review(day,size,concept,stock_codes) values(%s,%s,%s,%s)"
        datas = []
        if json_obj.get('status_code') != 0:
            return
        for item in json_obj.get('data').get('tab_list'):
            # print(item.get("tab_name")+",")
            tab_data = Objects.getJsonData(item, 'tab_data')
            cleanData = list(filter(lambda x: not x.get('ths_code').endswith("BJ"), tab_data))
            details = ",".join([self.convertStand(i.get('ths_code')) for i in cleanData])
            data = []
            data.append(tradingDay)
            data.append(len(cleanData))
            data.append(item.get('tab_name'))
            data.append(details)
            datas.append(data)
            # print("{name}({size}) {details}".format(name=item.get('tab_name'), size=len(tab_data), details=details))

        print(self.conn.insert_batch(sql, datas))

    def convertStand(self, stock):
        if stock.endswith("SZ"):
            return "sz." + stock.replace('.SZ', "")
        elif stock.endswith("SH"):
            return "sh." + stock.replace('.SH', "")
        else:
            return stock

    def query_to_df(self, endDay):
        print("query hot_review")
        startDay = Objects.to_db_date(Objects.getDay(-120))
        sql = ("select * from hot_review where day between {startDay} and {endDay} order by day".
               format(startDay=startDay, endDay=Objects.to_db_date(endDay)))
        return pd.DataFrame(self.conn.get_dicts(sql))
        # print(self.conn.get_values(sql))

    def to_excel(self, file, all_df):
        with pd.ExcelWriter(file) as writer:
            all_df.to_excel(writer, sheet_name='热点题材', index=False, freeze_panes=(1, 0), header=True)

    def convert_columns(self, all_df):
        days = all_df['day'].unique()
        all_data = []
        for day in days:
            df = all_df[all_df['day'] == day]
            if df.empty:
                return
            df = df.sort_values(by='size', ascending=False).reset_index()
            subjects = []  # count
            details = []  # details
            subjects.append(day)
            for index in range(10):
                row_data = None
                row_stocks = None
                for sub_index, row in df.iterrows():
                    if sub_index == index:
                        stocks = row['stock_codes'].split(",")
                        row_data = "{content}({size})".format(content=row['concept'], size=str(row['size']))
                        row_stocks = ",".join([StockBase().getStockName(code) for code in sorted(stocks)])
                subjects.append(row_data if row_data else '')
                details.append(row_stocks if row_stocks else '')

            cols = subjects + [' '] + details
            all_data.append(cols)

        return pd.DataFrame(all_data,
                            index=days,
                            columns=['day',
                                     '第一', '第二', '第三', '第四', '第五', '第六', '第七', '第八', '第九', '第十',
                                     '|',
                                     '第一股名', '第二股名', '第三股名', '第四股名', '第五股名', '第六股名', '第七股名',
                                     '第八股名', '第九股名', '第十股名'])


if __name__ == '__main__':
    hotReview = StockHotReview()
    hotReview.save_data(Objects.getCurrDay())
    if True:
        df = hotReview.query_to_df(endDay=Objects.getCurrDay())
        src_df = hotReview.convert_columns(df)
        str = "data/%s_stock.xlsx" % (Objects.getCurrDay())
        hotReview.to_excel(str, src_df)
